﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'usp_UPDMS_COM_PGM_ACCESS_LOG_Get_Access_Log')
	BEGIN
		DROP Procedure usp_UPDMS_COM_PGM_ACCESS_LOG_Get_Access_Log
	END
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************
**	Name : usp_UPDMS_COM_PGM_ACCESS_LOG_Get_Access_Log
**	Desc : 사용자별 접속로그 조회
**	Test Exec Query : exec usp_UPDMS_COM_PGM_ACCESS_LOG_Get_Access_Log '2013-04-01','2013-11-30','','Dir_Depth','ko'
**	Called by : Com_Dac_UPDMS_COM_PGM.cs
**	Program ID : Com_Dac_UPDMS_COM_PGM
**	Auth : 송시명
**	Date : 2013-06-20
*******************************************************************************
**	Change History
*******************************************************************************
**	Date:		Author:		Description:
**	--------	--------	---------------------------------------
**	
*******************************************************************************/
CREATE PROC [dbo].[usp_UPDMS_COM_PGM_ACCESS_LOG_Get_Access_Log]
@ls_reg_dt_from nvarchar(10),
@ls_reg_dt_to nvarchar(10),
@ls_user_id nvarchar(20),
@ls_view_order nvarchar(max),
@ls_lang_set nvarchar(2)
AS
SET NOCOUNT ON

DECLARE @sql nvarchar(max)

--전체조회
IF @ls_user_id = ''
BEGIN

SET @sql = '
SELECT CASE rslt.Pgm_Div WHEN ''D'' THEN rslt.Pgm_Nm
                         ELSE ''　　'' + rslt.Pgm_Nm
       END [Pgm_Nm],
       CASE rslt.Pgm_Div WHEN ''D'' THEN ''''
                         ELSE rslt.Pgm_Id
       END [Pgm_Id],
	   rslt.Pgm_Nm AS [Pgm_Nm_Label],
       '''' AS Nm,
       rslt.Access_Dt AS Last_Access_Dt,
	   CASE rslt.Pgm_Div WHEN ''D'' THEN 0
                         ELSE rslt.Cnt
       END Cnt,
	   LEFT(Dir_Depth, 3) + Pgm_Div AS View_Order,
	   Dir_Depth
  FROM (
       SELECT ucp.Pgm_Id,
              CASE @ls_lang_set WHEN ''ko'' THEN ucp.Pgm_Nm
                                ELSE ucp.Pgm_Nm_Eng
              END Pgm_Nm,
              ucp.Pgm_Div,
              ucp.Dir_Depth,
              tmp.Access_Dt,
			  tmp.Cnt
         FROM UPDMS_COM_PGM AS ucp WITH(NOLOCK)
         JOIN (
              SELECT Pgm_Id,
                     0 AS Cnt,
                     NULL AS Access_Dt
                FROM UPDMS_COM_PGM WITH(NOLOCK)
               WHERE Pgm_Div = ''D''
                 AND Pgm_Class IN (
                       SELECT LEFT(Pgm_Id, 3)
                         FROM UPDMS_COM_PGM_ACCESS_LOG WITH(NOLOCK)
                        WHERE Access_Dt >= @ls_reg_dt_from + '' 00:00:00.000''
                          AND Access_Dt <= @ls_reg_dt_to + '' 23:59:59.999''
                     )
              UNION ALL
              SELECT Pgm_Id,
                     COUNT(Seq) AS Cnt,
                     MAX(Access_Dt) AS Access_Dt
                FROM UPDMS_COM_PGM_ACCESS_LOG WITH(NOLOCK)
               WHERE Access_Dt >= @ls_reg_dt_from + '' 00:00:00.000''
                 AND Access_Dt <= @ls_reg_dt_to + '' 23:59:59.999''
               GROUP BY Pgm_Id
              ) tmp
           ON ucp.Pgm_Id = tmp.Pgm_Id
       ) rslt
 ORDER BY View_Order, ' + @ls_view_order
END
--사용자별 조회
ELSE
BEGIN

SET @sql = '
SELECT CASE rslt.Pgm_Div WHEN ''D'' THEN rslt.Pgm_Nm
                         ELSE ''　　'' + rslt.Pgm_Nm
       END [Pgm_Nm],
       CASE rslt.Pgm_Div WHEN ''D'' THEN ''''
                         ELSE rslt.Pgm_Id
       END [Pgm_Id],
	   rslt.Pgm_Nm AS [Pgm_Nm_Label],
       Nm,
       rslt.Access_Dt AS Last_Access_Dt,
	   CASE rslt.Pgm_Div WHEN ''D'' THEN 0
                         ELSE rslt.Cnt
       END Cnt,
	   LEFT(Dir_Depth, 3) + Pgm_Div AS View_Order,
	   Dir_Depth
  FROM (
       SELECT ucp.Pgm_Id,
              CASE @ls_lang_set WHEN ''ko'' THEN ucp.Pgm_Nm
                                ELSE ucp.Pgm_Nm_Eng
              END Pgm_Nm,
              ucp.Pgm_Div,
              ucp.Dir_Depth,
              tmp.Nm,
              tmp.Access_Dt,
			  tmp.Cnt
         FROM UPDMS_COM_PGM AS ucp WITH(NOLOCK)
         JOIN (
              SELECT '''' AS User_Id,
                     Pgm_Id,
                     '''' AS Nm,
                     0 AS Cnt,
                     NULL AS Access_Dt
                FROM UPDMS_COM_PGM WITH(NOLOCK)
               WHERE Pgm_Div = ''D''
                 AND Pgm_Class IN (
                       SELECT LEFT(Pgm_Id, 3)
                         FROM UPDMS_COM_PGM_ACCESS_LOG WITH(NOLOCK)
                        WHERE Access_Dt >= @ls_reg_dt_from + '' 00:00:00.000''
                          AND Access_Dt <= @ls_reg_dt_to + '' 23:59:59.999''
                          AND [User_Id] = @ls_user_id
                     )
              UNION ALL
              SELECT ucpal.User_Id,
                     ucpal.Pgm_Id,
                     ucum.Nm,
                     COUNT(ucpal.Seq) AS Cnt,
                     MAX(ucpal.Access_Dt) AS Access_Dt
                FROM UPDMS_COM_PGM_ACCESS_LOG ucpal WITH(NOLOCK)
                JOIN UPDMS_COM_USER_MGT ucum WITH(NOLOCK) ON ucpal.User_Id = ucum.User_Id
               WHERE ucpal.Access_Dt >= @ls_reg_dt_from + '' 00:00:00.000''
                 AND ucpal.Access_Dt <= @ls_reg_dt_to + '' 23:59:59.999''
                 AND ucpal.User_Id = @ls_user_id
               GROUP BY ucpal.User_Id, ucum.Nm, ucpal.Pgm_Id
              ) tmp
           ON ucp.Pgm_Id = tmp.Pgm_Id
       ) rslt
 ORDER BY View_Order, ' + @ls_view_order
END

DECLARE @param nvarchar(max)
SET @param = '
  @ls_reg_dt_from nvarchar(10),
  @ls_reg_dt_to nvarchar(10),
  @ls_user_id nvarchar(20),
  @ls_view_order nvarchar(max),
  @ls_lang_set nvarchar(2)
'

--print(@sql)
EXEC sp_executesql @sql, @param,
                   @ls_reg_dt_from = @ls_reg_dt_from,
                   @ls_reg_dt_to = @ls_reg_dt_to,
                   @ls_user_id = @ls_user_id,
                   @ls_view_order = @ls_view_order,
                   @ls_lang_set = @ls_lang_set


--사용자별 접속분포도
SELECT ucpal.User_Id,
       ucum.Nm,
       COUNT(ucpal.Seq) AS Cnt
  FROM UPDMS_COM_PGM_ACCESS_LOG ucpal WITH(NOLOCK)
  JOIN UPDMS_COM_USER_MGT ucum WITH(NOLOCK) ON ucpal.User_Id = ucum.User_Id
 WHERE ucpal.Access_Dt >= @ls_reg_dt_from + ' 00:00:00.000'
   AND ucpal.Access_Dt <= @ls_reg_dt_to + ' 23:59:59.999'
 GROUP BY ucpal.User_Id, ucum.Nm


--프로그램별 접속분포도(상위 10개)
SELECT TOP 10 ucp.Pgm_Id,
       ucp.Pgm_Nm,
       COUNT(ucpal.Seq) AS Cnt
  FROM UPDMS_COM_PGM ucp WITH(NOLOCK)
  JOIN UPDMS_COM_PGM_ACCESS_LOG ucpal WITH(NOLOCK) ON ucp.Pgm_Id = ucpal.Pgm_Id
 WHERE ucpal.Access_Dt >= @ls_reg_dt_from + ' 00:00:00.000'
   AND ucpal.Access_Dt <= @ls_reg_dt_to + ' 23:59:59.999'
 GROUP BY ucp.Pgm_Id, ucp.Pgm_Nm
 ORDER BY COUNT(ucpal.Seq) DESC

GO